﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Data;
using SqlMiM.SqlServer.Model;
using SqlMiM.Common;
using SqlMiM.Common.Core;
using SqlMiM.Common.Model;
using SqlMiM.Common.Trace;

namespace SqlMiM.SqlServer.Core
{
    public class ServerInfoServices
    {
        #region Singleton

        private static ServerInfoServices current;

        public static ServerInfoServices Current
        {
            get
            {
                if (current == null)
                {
                    current = new ServerInfoServices();
                }

                return current;
            }
        }

        #endregion

        private SqlCommand selectServerInfos2Command;
        private SqlCommand selectServerIOCommand;

        public ServerInfoServices()
        {
        }

      

        public ServerInfo GetServerInfos()
        {
            ServerInfo serverInfos = null;
            SqlConnection connection = Context.Current.GetConnection();
            SqlDataReader reader = null;


            try
            {

                ServerServices.Current.SelectServerInfosCommand.Connection = connection;
                this.SelectServerInfos2Command.Connection = connection;
                this.SelectServerIOCommand.Connection = connection;

                connection.Open();

                reader = ServerServices.Current.SelectServerInfosCommand.ExecuteReader();

                if (!reader.HasRows)
                    return null;

                serverInfos = new ServerInfo();

                while (reader.Read())
                {
                    Server.FillFromReader(reader, serverInfos);
                }

                reader.Close();

                reader = this.SelectServerInfos2Command.ExecuteReader();

                while (reader.Read())
                {
                    serverInfos.LoginUpTime = reader["LoginUpTime"] != System.DBNull.Value ? (DateTime)reader["LoginUpTime"] : DateTime.Now;
                    serverInfos.ActiveSessionsCount = reader["ActiveSessionsCount"] != System.DBNull.Value ? (Int32)reader["ActiveSessionsCount"] : 0;
                    serverInfos.ActiveTransactionCount = reader["ActiveTransactionCount"] != System.DBNull.Value ? (Int32)reader["ActiveTransactionCount"] : 0;
                    serverInfos.TotalServerMemory = reader["TotalServerMemory"] != System.DBNull.Value ? (Int32)reader["TotalServerMemory"] : 0;
                    serverInfos.UserConnections = reader["UserConnections"] != System.DBNull.Value ? (Int32)reader["UserConnections"] : 0;
                    serverInfos.LogicalConnections = reader["LogicalConnections"] != System.DBNull.Value ? (Int32)reader["LogicalConnections"] : 0;
                    serverInfos.DatabasesCount = reader["DatabasesCount"] != System.DBNull.Value ? (Int32)reader["DatabasesCount"] : 0;
                }

                reader.Close();
                
                reader = this.SelectServerIOCommand.ExecuteReader();

                while (reader.Read())
                {
                    ServerInputOutput io = new ServerInputOutput();
                    io.DatabaseName = reader["dbname"] as String;
                    io.TotalIO = reader["total_io"] != System.DBNull.Value ? (Int64)reader["total_io"] : (Int64)0;
                    io.TotalReads = reader["total_logical_reads"] != System.DBNull.Value ? (Int64)reader["total_logical_reads"] : (Int64)0;
                    io.TotalWrites = reader["total_logical_writes"] != System.DBNull.Value ? (Int64)reader["total_logical_writes"] : (Int64)0;
                    serverInfos.InputsOutuputs.Add(io);
                }


                reader.Close();

                connection.Close();


            }
            catch (SqlException sqlEx)
            {
                Notifier.Current.Notify(sqlEx);
            }
            catch (Exception ex)
            {
                Notifier.Current.Notify(ex);
            }
            finally
            {

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }

                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
            }

            return serverInfos;

        }


        internal SqlCommand SelectServerInfos2Command
        {
            get
            {
                if (selectServerInfos2Command == null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("    Declare @DatabasesCount int, @LoginUpTime DateTime, @ActiveSessionsCount int, @ActiveTransactionCount int, @TotalServerMemory int, @UserConnections int, @LogicalConnections int; ");


                    sb.AppendLine("    select @LoginUpTime = login_time from sys.sysprocesses where spid=1 ");

                    sb.AppendLine("    Select @ActiveSessionsCount = COUNT(distinct(s.session_id))  ");
                    sb.AppendLine("    from sys.dm_exec_sessions s where s.is_user_process = 1 and s.status='running' ");
	
                    sb.AppendLine("    Select @ActiveTransactionCount = COUNT(at.transaction_id) ");
                    sb.AppendLine("    from sys.dm_tran_active_transactions at  ");
                    sb.AppendLine("    where at.transaction_state = 2 or ( at.transaction_type = 4 and at.dtc_state = 1) ");
	
                    sb.AppendLine("    Select @TotalServerMemory = cntr_value ");
                    sb.AppendLine("    from sys.dm_os_performance_counters at  ");
                    sb.AppendLine("    where counter_name like '%Total Server Memory (KB)%' ");
	
                    sb.AppendLine("    Select @UserConnections = cntr_value ");
                    sb.AppendLine("    from sys.dm_os_performance_counters at  ");
                    sb.AppendLine("    where counter_name like '%User Connections%' ");

                    sb.AppendLine("    Select @LogicalConnections = cntr_value ");
                    sb.AppendLine("    from sys.dm_os_performance_counters at  ");
                    sb.AppendLine("    where counter_name like '%Logical Connections%' ");

                    sb.AppendLine("    Select @DatabasesCount = COUNT(*)  from sys.databases where state = 0 ");

                    sb.AppendLine("    Select	@ActiveSessionsCount as ActiveSessionsCount,  ");
                    sb.AppendLine("    		@ActiveTransactionCount as ActiveTransactionCount, ");
                    sb.AppendLine("    		@TotalServerMemory as TotalServerMemory, ");
                    sb.AppendLine("    		@UserConnections as UserConnections, ");
                    sb.AppendLine("    		@LogicalConnections as LogicalConnections, ");
                    sb.AppendLine("    		@DatabasesCount as DatabasesCount,  ");
                    sb.AppendLine("    		@LoginUpTime as LoginUpTime  ");

                    selectServerInfos2Command = new SqlCommand();
                    selectServerInfos2Command.CommandType = CommandType.Text;
                    selectServerInfos2Command.CommandText = sb.ToString();

                }

                return selectServerInfos2Command;
            }
        }


        internal SqlCommand SelectServerIOCommand
        {
            get
            {
                if (selectServerIOCommand == null)
                {
                    StringBuilder sb = new StringBuilder();


                    sb.AppendLine("    ;With mystats as (select	 "); 
                    sb.AppendLine("    		total_logical_reads, ");
                    sb.AppendLine("    		total_logical_writes, ");
                    sb.AppendLine("    		(total_logical_reads + total_logical_writes) as  total_io, ");
                    sb.AppendLine("    		case when db_name(dbid) is null then 'Adhoc Queries' else  db_name(dbid) end as dbname ");
                    sb.AppendLine("    from sys.dm_exec_query_stats s1  ");
                    sb.AppendLine("    cross apply sys.dm_exec_sql_text(sql_handle) as  s2) ");
                    sb.AppendLine("    Select	dbname, ");
                    sb.AppendLine("    		SUM(total_logical_reads) as total_logical_reads, ");
                    sb.AppendLine("    		SUM(total_logical_writes) as total_logical_writes, ");
                    sb.AppendLine("    		SUM(total_io) as total_io ");
                    sb.AppendLine("    		from mystats ");
                    sb.AppendLine("    Group By dbname ");
                    sb.AppendLine("    Order By total_io desc ");

                    selectServerIOCommand = new SqlCommand();
                    selectServerIOCommand.CommandType = CommandType.Text;
                    selectServerIOCommand.CommandText = sb.ToString();

                }

                return selectServerIOCommand;
            }
        }

    }
}
